﻿Create PROCEDURE [dbo].[uspCursorEx]

AS
	DECLARE
     @schema_name SYSNAME,
     @object_name SYSNAME,
     @index_name  SYSNAME,
     @ss NVARCHAR(MAX) = N'';

DECLARE indexes CURSOR
  LOCAL FAST_FORWARD READ_ONLY
     FOR
         SELECT s = OBJECT_SCHEMA_NAME(o.[object_id]), o = o.name, i = i.name
            FROM sys.objects AS o
            INNER JOIN sys.indexes AS i
            ON o.[object_id] = i.[object_id]
            INNER JOIN
            (
               SELECT [object_id], index_id, row_count = SUM(row_count)
                  FROM sys.dm_db_partition_stats
                  GROUP BY [object_id], index_id
             ) AS s
             ON o.[object_id] = s.[object_id]
             AND i.index_id = s.index_id
             WHERE o.is_ms_shipped = 0
             AND i.index_id >= 1
             ORDER BY s.row_count DESC, s, o, i;

OPEN indexes;

FETCH NEXT FROM indexes INTO @schema_name, @object_name, @index_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- we're just concatenating here, but pretend we needed to, 
    -- say, call a stored procedure for each row in the cursor:

     SET @ss += CHAR(13) + CHAR(10) + N'ALTER INDEX '
             + QUOTENAME(@index_name)  + ' ON '
             + QUOTENAME(@schema_name) + '.'
             + QUOTENAME(@object_name) + ' REORGANIZE;';

     FETCH NEXT FROM indexes INTO @schema_name, @object_name, @index_name;
END

CLOSE indexes;
DEALLOCATE indexes;
RETURN 0